package com.hs.app.dataprocess;

import java.io.File;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.hs.core.db.DBMgr;
import com.hs.core.imp.ExcelImportUtil;
import com.hs.core.utils.UUIDGenerator;

public class DisCatagoryHelper {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
//		String filepath = "f:\\个案库疾病分类.xls";
//		ExcelImportUtil inst = ExcelImportUtil.getInstance(new File(filepath));
//		HSSFWorkbook workbook = null;
//		try {
//				workbook = inst.readFile();
//				int total = workbook.getNumberOfSheets(); //获取sheet个数      
//		        int start = 0;
//		        int end = 0 ;   
//		        //获取sheet个数
//				for(int i = 0 ; i < 1 ; i ++){  
//		            HSSFSheet sheet = workbook.getSheetAt(i);// 按索引获取sheet引用   
//		            start = sheet.getFirstRowNum();  //sheet起始行索引      
//		            end = sheet.getLastRowNum();//sheet起结束行索引      
//		            int counter = 1;    
//		            HSSFRow row = null ; 
//		            HSSFCell firstcol = null ; 
//		        	HSSFCell secondcol = null ;  
//		        	HSSFCell thirdcol = null ; 
//		        	HSSFCell fourcol = null ; 
//		        	int pid = 0;
//		        	DBMgr dbo = DBMgr.getDbUtilInst();
//		            while(counter <= end){      
// 		                 row =  sheet.getRow(counter);//获取第N行表格数据     
//		                 if(row != null){
//		                	  counter ++ ;
//		                	  firstcol = row.getCell(0);//获取指定列单元格            
//		                	  secondcol = row.getCell(1);   
//		                	  thirdcol = row.getCell(2);
//		                	  fourcol = row.getCell(3);
//		                	  String onecontent = firstcol.getStringCellValue();
//		                	  String twocontent = secondcol.getStringCellValue();
//		                	  String threecontent = thirdcol.getStringCellValue();
//		                	  String fourcontent = fourcol.getStringCellValue();
//		                	  //System.out.println("col1:" + onecontent);
//		                	  System.out.println("col2:" + twocontent);
//		                	  //System.out.println("col3:" + threecontent);
//		                	  int uuid = getSeqValue(); 
//		                	  String cataname = onecontent;
//		                	  if(StringUtils.isNotEmpty(onecontent)){
//		                		  pid = uuid;
//		                		  cataname = onecontent;
//		                	  }else{
//		                		  cataname = twocontent;
//		                	  }
//		                	  String sql = "insert into cfcd_discatagory(ID,PID,CATANAME,DATATYPE,DISTYPE,SYNNAMESTR) " +
//		                	  		"  VALUES(?,?,?,?,?,?)";
//		                	  dbo.update(sql, new Object[]{uuid,pid,cataname,1,fourcontent,threecontent});
//		                	  
//		                 }
//		            }
//				}
//			}catch(Exception e){
//				e.printStackTrace();
//			}
		//initSynwords();
		//System.out.println(getSeqValue());
		DBMgr dbo = DBMgr.getDbUtilInst();
		String sql = "SELECT * FROM (select a.* from cfcd_index a  where  instr(huanzhexm,'路志正')>0  or instr(wenti,'路志正')>0  or instr(zhenghou,'路志正')>0  or instr(zhengzhuang,'路志正')>0  or instr(yaofangmc,'路志正')>0  or instr(xuewei,'路志正')>0  or instr(danweiymc,'路志正')>0  or instr(dname,'路志正')>0  or instr(zuozhexm,'路志正')>0  or instr(zhongyijbmc,'路志正')>0  or instr(xiyijbmc,'路志正')>0 ) f  LIMIT 1,15";
		System.out.println("==============:"+dbo.queryList(sql, null));
		}
	
	public static int getSeqValue(){
		DBMgr dbo = DBMgr.getDbUtilInst();
		int seqvalue = dbo.queryCountForInt("select nextval('seq_dis') as seqvalue ", null);
		return seqvalue;
	}
	public static void initSynwords(){
		DBMgr dbo = DBMgr.getDbUtilInst();
		List datalist = dbo.queryList("select * from cfcd_discatagory where pid !=id  ", null);
		for(int i = 0;i<datalist.size();i++){
			Map data = (Map)datalist.get(i);
			String key =  data.get("ID")+"";
			String cataname = (String)data.get("CATANAME");
			String synnamestr = (String)data.get("SYNNAMESTR");
			int countnum = dbo.queryCountForInt("select count(1) as countnum from CFCD_SYNONYMS where WORDNAME=?", new Object[]{cataname});
			int groupid = 0;
			if(countnum <= 0 ){
				int uuid = getSeqValue(); 
				groupid = uuid;
				dbo.update("insert into CFCD_SYNONYMS(id,GROUPID,WORDNAME,DATATYPE,OLDKEY) values(?,?,?,?,?)", new Object[]{uuid,groupid,cataname,1,key});
				if(StringUtils.isNotEmpty(synnamestr)){
					String[] namearr = synnamestr.split(";"); 
					for(int j =0;j<namearr.length;j++){
						String namestr = namearr[j];
						int uuid2 = getSeqValue(); 
						if(!cataname.equals(namestr)){
							int count = dbo.queryCountForInt("select count(1) as countnum from CFCD_SYNONYMS where WORDNAME=?", new Object[]{namestr});
                            if(count == 0){
                				dbo.update("insert into CFCD_SYNONYMS(id,GROUPID,WORDNAME,DATATYPE,OLDKEY) values(?,?,?,?,?)", new Object[]{uuid2,groupid,namestr,2,key});
                            }
						}
					}
				}
			}
		}
	}

}
